const db = require('./DBConnect');
const conn = db.connection;

const pagerHelper = require('../../util/PagerHelper');

/**
 * 自定义查询接口
 * @param sql   查询的sql命令
 * @param callback  结果回调处理
 * @returns {Promise<unknown>}
 */
function toPromise(sql, callback) {
	console.log('正在执行的sql：', sql);
	return new Promise((resolve, reject) => {
		conn.query(sql, (err, results) => {
			if (err) {
				reject(err);
			} else {
				// console.log("results", results);
				if (callback) callback(results, resolve);
				else resolve(results);
			}
		});
	});
}

function getInsertSqlFromObj(obj, table) {
	let sql = `insert into ${table}(`;
	for (let key of Object.keys(obj)) {
		sql += key + ',';
	}
	sql = sql.substring(0, sql.length - 1);
	sql += ') values(';
	for (let val of Object.values(obj)) {
		sql += "'" + val + "',";
	}
	sql = sql.substring(0, sql.length - 1);
	sql += ');';
	return sql;
}

function getUpdateSqlFromObj(obj, table) {
	let sql = `update ${table} set `;
	for (let [key, val] of Object.entries(obj)) {
		if (key == 'id') continue;
		sql += `${key} = '${val}', `;
	}
	sql = sql.substring(0, sql.lastIndexOf(','));
	sql += " where id = '" + obj.id + "'";
	return sql;
}

/**
 * 分页查询
 * @param table 表名
 * @param where where语句
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findByPage(table, where, columns) {
	return toPromise(`select ${columns ? columns : '*'} from ${table} ${where} limit ${(pagerHelper.pageNum - 1) * pagerHelper.pageSize}, ${pagerHelper.pageSize}`);
}

/**
 * 删除多个ids的数据
 * @param table 表名
 * @param ids   必须是数组
 * @returns {Promise<*>}
 */
function selectByIds(table, ids, columns) {
	console.log(ids);
	console.log(Array.isArray(ids));
	if (Array.isArray(ids)) {
		console.log('selectByIds');
		let str = '';
		ids.forEach((value) => {
			str += "'" + value + "',";
		});
		str = str.substring(0, str.length - 1);
		return toPromise(`select ${columns} from ${table} where s.id in (${str})`);
	}
}

/**
 * 非分页查询，所有条件自定义
 * @param table 表名
 * @param where where语句
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findBy(table, where, columns) {
	return toPromise(`select ${columns ? columns : '*'} from ${table} ${where ? where : ''} `);
}
/**
 *
 * @param {*} table
 * @param {*} pid
 * @param {*} columns
 * @returns
 */
function findBypid(table, pid, columns) {
	return toPromise(`select ${columns ? columns : '*'} from ${table} where pid = ${pid} `);
}

/**
 * 查询一条数据
 * @param table 表名
 * @param where where语句
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findOne(table, where, columns) {
	return toPromise(`select ${columns ? columns : '*'} from ${table} ${where}`, (results, resolve) => {
		resolve(results ? results[0] : null);
	});
}

/**
 * 根据id搜索
 * @param id
 * @param table 表名
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findById(id, table, columns) {
	return toPromise(`select ${columns ? columns : '*'} from ${table} where id = ${id}`, (results, resolve) => {
		resolve(results ? results[0] : null);
	});
}

/**
 * 根据users的id从users、address两表搜索
 * @param id
 * @param table 表名
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findAddress(id, table, columns) {
	return toPromise(`select ${columns ? columns : '*'} from address a join ${table} b on a.uid = b.id where b.id = ${id}`, (results, resolve) => {
		resolve(results ? results[0] : null);
	});
}

/**
 * 查询数据总数
 * @param table 表名
 * @param where where语句
 * @returns {Promise<*>}
 */
function getCount(table, where) {
	return toPromise(`select count(1) as count from ${table} ${where}`, (results, resolve) => {
		resolve(results[0].count);
	});
}

/**
 * 根据id删除数据
 * @param table 表名
 * @param id
 * @returns {Promise<unknown>}
 */
function deleteById(table, id) {
	return toPromise(`delete from ${table} where id = ${id}`);
}

function deleteFakeById(table, id) {
	return toPromise(`update ${table} set state=0 where id = ${id}`);
}

function setDefaultAddress(id, uid) {
	return toPromise(`update address set isdefault=0 where ${id?' id != '+id+' and ': ''} uid =${uid}`);
}
/**
 * 删除多个ids的数据
 * @param table 表名
 * @param ids   必须是数组
 * @returns {Promise<*>}
 */
function deleteByIds(table, ids) {
	if (Array.isArray(ids)) {
		let str = '';
		ids.forEach((value) => {
			str += "'" + value + "',";
		});
		str = str.substring(0, str.length - 1);
		return toPromise(`delete from ${table} where id in (${str})`);
	}
}

/**
 * 删除多个ids的数据
 * @param table 表名
 * @param ids   必须是数组
 * @returns {Promise<*>}
 */
function deleteFakeByIds(table, ids) {
	if (Array.isArray(ids)) {
		let str = '';
		ids.forEach((value) => {
			str += "'" + value + "',";
		});
		str = str.substring(0, str.length - 1);
		return toPromise(`update ${table} set state=0 where in = ${str}`);
	}
}

/**
 * 插入数据到数据库中
 * @param table 表名
 * @param obj
 * @returns {Promise<EnforceDocument<unknown, {}>[]>}
 */
function insert(table, obj) {
	return toPromise(getInsertSqlFromObj(obj, table));
}

/**
 * 更新
 * @param table 表名
 * @param obj
 * @returns {Promise<*>}
 */
function update(table, obj) {
	return toPromise(getUpdateSqlFromObj(obj, table));
}

module.exports = {
	setDefaultAddress,
	findByPage,
	findById,
	findOne,
	getCount,
	deleteById,
	deleteFakeById,
	deleteByIds,
	deleteFakeByIds,
	insert,
	update,
	toPromise,
	findBy,
	selectByIds,
	findBypid,
	findAddress,
};
